زمانی که XML در عمل استانداردی برای تبادلات دادهها شده بود، SQL Server 2005 پشتیبانی محلی از ذخیره سازی، مدیریت و پردازش دادهها را معرفی کرد. این ویژگی هنوز در SQL Server وجود دارد چراکه XML امتیازات به ارث بردهای دارد و همچنان به صورت گسترده مورد استفاده قرار میگیرد، اما از آنجاییکه اکثر اپلیکیشنهای جدید برای مکانیزم تبادل دادههای سبک، استفاده از JSON را آغاز کردهاند؛ SQL Server 2016 پشتیبانی توکار را برای ذخیره سازی، مدیریت و پردازش دادههای JSON معرفی میکند. در این مقاله این ویژگی جدید را شرح میدهیم و نشان میدهیم که چگونه میتوانید از آن در اپلیکیشنهای خود در سناریوهای مختلف استفاده نمایید.
پردازش دادههای JSON در SQL Server
SQL server 2016 پشتیبانی توکار برای ذخیره سازی، مدیریت و تجزیه دادههای JSON را معرفی کرد. گرچه امکان ذخیره ، مدیریت و تجزیه دادههای JSON حتی در ورژنهای قبلی SQL Server وجود دارد اما اجرای آن مستلزم تلاش بیشتری در بخش توسعه است. با وجود پشتیبانی توکار میتوانید این کار را خیلی سریع تر انجام دهید ضمن این که به جای نوشتن توابع برای تجزیه دادههای JSON میتوانید روی تجزیه دادههای خود یا منطق اپلیکیشن خود تمرکز کنید.
JSON مخفف JavaScript Object Notation میباشد و یک فرمت سبک برای تبادل دادههای متنی خواندنی برای انسان میباشد که مبتنی بر زبان برنامه نویسی JavaScript است (گرچه این زبان برنامه نویسی مستقل از زبان میباشد و امروزه در اکثر زبانهای برنامه نویسی مورد استفاده قرار میگیرد).
با این ویژگی جدید میتوانید از طریق دادههای جدولی موجود در جدولهای رابطهای، فرمت خود را به JSON تبدیل نمایید و نیز دادههای JSON را تجزیه کنید و آنها را برای گزارش گیری، پیوند با جدولهای دیگر یا انتقال به اپلیکیشنهای دیگر که منتظر دادههای جدولی هستند، به قالب فرمت جدولی در بیاورید.
برخلاف پشتیبانی بومی XML، جایی که نوع دادههای XML را برای ذخیره کردن دادهها یا اسناد XML دارید، برای ذخیره کردن دادهها یا اسناد JSON در SQL Server 2016 ، از دادههای NVARCHAR استفاده میکنید. این یعنی شما محدود نیستید و هرکجا که NVARCHAR پشتیبانی میشود، میتوانید دادههای JSON را تقریباً در هرجایی ذخیره و تجزیه نمایید. همچنین، نیاز به شاخص خاصی در داده های JSON ندارید و همچنان میتوانید از شاخصهای متداول که با آنها آشنایی دارید، استفاده نمایید.
صادر کردن دادههای جدولی به صورت دادههای JSON
SQL Server 2016 عبارت FOR JSON [ AUTO | PATH ] را معرفی میکند تا با پرس و جوی شما استفاده شود و آن را قبل از بازگرداند به سرویس گیرنده در قالب فرمت JSON فرمت نماید.اگر از قبل تجربه کار کردن با عبارت FOR XML را دارید، احتمالا خواهید فهمید که این عبارت جدید شبیه همین عبارت FOR XML است.
بگذارید این را با مثال نشان دهیم. فرض کنید که پایگاه اطلاعاتی AdventureWorks2014 را دارید. میتوانید این پرس و جو را اجرا کنید تا محصولاتی را که در این دو سفارش خاص سفارش داده شده اند را اجرا نمایید. همانطور که در تصویر زیر میبینید برای OrderID 43663 تنها یک محصول وجود دارد در حالیکه برای OrderID 43687 دو محصول.
SELECT H.SalesOrderID, H.Status, H.PurchaseOrderNumber, H.ShipDate, P.ProductID
FROM [Sales].[SalesOrderHeader] H
INNER JOIN [Sales].SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
INNER JOIN [Production].[Product] P ON D.ProductID = P.ProductID
WHERE H.SalesOrderID IN (43663, 43687)
GO
پرس و جوی سفارش محصولات
حال عبارت FOR JSON AUTO را در انتهای پرس و جوی بالا اضافه میکنیم و آن را اجرا مینماییم. همانطور که در تصویر زیر میبینید، این بار SQL Server نتایج این پرس و جو را میگیرد، آن را به صورت سند JSON فرمت میکند و در نهایت به سرویس گیرنده باز میگرداند.
SELECT H.SalesOrderID, H.Status, H.PurchaseOrderNumber, H.ShipDate, P.ProductID
FROM [Sales].[SalesOrderHeader] H
INNER JOIN [Sales].SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
INNER JOIN [Production].[Product] P ON D.ProductID = P.ProductID
WHERE H.SalesOrderID IN (43663, 43687)
FOR JSON AUTO
GO
پرس و جوی فرمت شده به صورت سند JSON
SQL Server بنا به دلایل مشخصی، خروجی JSON را با اضافه کردن فضاهای خالی مثل تبها یا فاصلهها فرمت نمیکند، گرچه میتوانید برای بهتر شدن قابلیت خواندن، این کار را به صورت دستی انجام دهید یا از یک ابزار آنلاین یا محلی برای فرمت کردن آن استفاده نمایید. برای یافتن یکی از این ابزارهای آنلاین جهت فرمت کردن خروجی JSON از این آدرس استفاده کنید: https://jsonformatter.curiousconcept.com/
وقتی دادههای JSON را از پرس وجوی بالا فرمت میکنید، متوجه خواهید شد که SQL Server با عبارت FOR JSON AUTO ، به طور خودکار خروجی JSON را بر اساس ساختار پرس وجو فرمت کرده است ( ترتیب ستون ها در لیست SELECT و جدول منبع آنها).
در این مورد، از آنجاییکه SalesOrderHeader اولین جدول است و SalesOrderDetail دومین جدول است، ستونها از SalesOrderHeader به صورت خصوصیات آبجکت والد ایجاد میشوند در حالیکه ستونها از SalesOrderDetail به عنوان خصوصیات آبجکت تو در تو:
SQL Server به طور خودکار خروجی JSON را فرمت کرده است
عبارت FOR JSON AUTO برای اکثر سناریوها مناسب است، اما این احتمال وجود دارد که در برخی سناریوهای خاص بخواهید روی نحوه ساخته شدن یا تودرتو شدن دادههای JSON کنترل داشته باشید. عبارتFOR JSON PATH به شما کنترل تام میدهد تا فرمت خروجی دادههای JSON را مشخص نمایید، به شما امکان میدهد آبجکتهای wrapper بسازید و خصوصیات پیچیده را تودرتو نمایید.
اگر پرس وجوی شما شامل دو یا چند جدول است، به طور پیش فرض یک نتیجه یکنواخت را باز میگرداند بطوریکه هر ستون در نهایت خصوصیتی از آبجکت JSON میشود.
شما میتوانید برای نتایج تودرتو از نامهای ستونی جداشده با نقطه استفاده کنید؛ در مورد زیر ما از Order به عنوان نام آبجکت استفاده کردیم و نتیجه این شد:
SELECT H.SalesOrderID AS ‘Order.OrderID’, H.Status AS ‘Order.Status’,
H.PurchaseOrderNumber AS ‘Order.PONumber’, H.ShipDate AS ‘Order.ShipDate’,
P.ProductID AS ‘Order.ProductDI’
FROM [Sales].[SalesOrderHeader] H
INNER JOIN [Sales].SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
INNER JOIN [Production].[Product] P ON D.ProductID = P.ProductID
WHERE H.SalesOrderID IN (43663, 43687)
FOR JSON PATH
شما میتوانید با استفاده از نامهای ستون جداشده با نقطه به همراه پسوند متفاوت یا نام آبجکت ( با مشخص کردن نامهای مستعار ستون که ساختار دادههای JSON شما را تعریف میکنند) نتیجه را تودرتو کنید. برای مثال، در این مورد شما یک آبجکت تراز بالا دارید که دارای دو آبجکت Order و Product میباشد که در داخل تودرتو شدهاند. با هر کدام از آبجکتها ما برخی خصوصیات خاص را داریم که از داخل کپسول سازی شدهاند.
SELECT H.SalesOrderID AS ‘Order.OrderID’, H.Status AS ‘Order.Status’,
H.PurchaseOrderNumber AS ‘Order.PONumber’, H.ShipDate AS ‘Product.ShipDate’,
P.ProductID AS ‘Product.ProductID’
FROM [Sales].[SalesOrderHeader] H
INNER JOIN [Sales].SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
INNER JOIN [Production].[Product] P ON D.ProductID = P.ProductID
WHERE H.SalesOrderID IN (43663, 43687)
FOR JSON PATH
GO
نتیجه استفاده از نامهای ستون جدا شده با نقطه
به طور پیش فرض، خروجی JSON شامل یک عنصر ریشه نیست و بنابراین میتوانید از کلمه کلیدی ROOT با عبارت FOR JSON AUTO یا FOR JSON PATH استفاده کنید تا یک عنصر تراز بالا و منحصر را به خروجی JSON بیافزایید. مثلاً با پرس وجوی زیر یک عنصر تراز بالای Orders را اضافه میکنیم و نتیجه به صورت یک آرایه از داخل تودرتو خواهد شد، همانطور که در تصویر زیر میبینید.
ممکن است متوجه شده باشید که برخلاف موارد قبلی، این بار عنصر بیرونی از یک آرایه به یک آبجکت تغییر یافته است که شامل Order به عنوان خصوصیت میباشد، این خصوصیت بیشتر شامل آرایه آبجکتهاست (Order و Product ):
SELECT H.SalesOrderID AS ‘Order.OrderID’, H.Status AS ‘Order.Status’,
H.PurchaseOrderNumber AS ‘Order.PONumber’, H.ShipDate AS ‘Product.ShipDate’,
P.ProductID AS ‘Product.ProductID’
FROM [Sales].[SalesOrderHeader] H
INNER JOIN [Sales].SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
INNER JOIN [Production].[Product] P ON D.ProductID = P.ProductID
WHERE H.SalesOrderID IN (43663, 43687)
FOR JSON PATH, ROOT (‘Orders’)
GO
نتایج تودرتو شده داخل یک آرایه
همانند ROOT ، شما میتوانید از کلمه کلیدی INCLUDE_NULL_VALUES با عبارت FOR JSON AUTO یا FOR JSON PATH استفاده کنید تا مقادیر null ( هماند خصوصیات JSON) را در خروجی JSON بگنجانید، چراکه آنها به طور پیش فرض در خروجی گنجانده نشدهاند.
توجه: ویژگی ذکر شده و به نمایش درآمده در این مقاله بر مبنای SQL Server 2016 CTP 3.2 است و ممکن است در RTM موجود یا در نسخههای بعدی تغییر کند.
نتیجه
SQL Server 2016 پشتیبانی توکار برای ذخیره سازی، مدیریت و تجزیه دادههای JSON را معرفی میکند. در این مقاله در مورد پشتیبانی از JSON در SQL Server 2016 صحبت کردیم و نحوه فرمت کردن یا تبدیل دادههای جدولی به فرمت JSON با استفاده ازعبارت FOR JSON را عنوان نمودیم. همچنین نگاهی داشتیم به انواع مختلف FOR JSON ،استفاده از AUTO برای دریافت خودکار ساختار دادههای JSON بر اساس ترتیب جدولهای منبع و ستونها در پرس و جو یا با استفاه از PATH برای داشتن کنترل کامل در تعیین ساختار خروجی دادههای JSON.